A database management system (DBMS) is a computer based system that stores data in tables and retrieves such stored data. Storage, retrieval and removal of data is accomplished using statements provided to the DBMS in a database query language such as Structured Query Language (SQL). The statement is incorporated into the DBMS, by such means as parsing, where it is converted into an internal representation that provides a summary of the semantic relationships of the statement for optimization purposes. This internal representation is optimized to provide an efficiently executable plan for the statement which specifies the manner in which data is to be retrieved or modified.
The internal representation of a statement has sequencing operation when there are modifying operations (e.g. insert, delete or update) in the statement and there is an evaluation order in which these modifying operations and reading operations are to be executed for the purpose of satisfying semantic specifications of the statement. For example, the statement may be a query that includes multiple subexpressions using a WITH clause therein, each of which may produce some result that is used to form the arguments of a main SELECT statement. Each of the subexpressions in the WITH clause, also known as a common table expression, may be ordered such that consistent results are produced by the statement over multiple execution iterations. Such an evaluation order may be used, for example, when there are modifying operations (e.g. insert, delete or update) in any of the subexpressions that could potentially change evaluation results of the statement. A sequencing operation drives evaluation of each of the subexpressions according to the evaluation order. Another example of a subexpression is a modifying statement (e.g. insert, delete or update) whose internal representation uses a compilation of system-generated subexpressions to satisfy the semantic specifications of a table being modified. The evaluation order of such a subexpressions and the original modifying operation is controlled by the sequencing operation. Such subexpressions whose evaluation order is controlled by a sequencing operation are referred to as sequenced subexpressions.
In a graphical depiction of the internal representation, each of the subexpressions and the sequencing operation are represented with data flows between them. If the data flow in the graph is oriented vertically, the sequencing operation has data flowing arcs showing the flow of data over each of the subexpressions. Each arc and subexpression combination (possibly formed of multiple arcs and subexpressions) that stems from the sequencing operation is considered to be a sequenced subgraph.
Any operation or command with more than one data flowing arc over it is known as a common subexpression. In a common subexpression results of one operation are used by another operation resulting in temporary storage of the intermediate result to maintain data content consistency. Thus, extra storage is used during execution of the statement and execution time increases.